/*
 * MySqlConnection.java
 *
 * Created on December 27, 2005, 4:44 PM
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */
package sorm;

import java.sql.*;
import java.util.*;
//import com.mysql.jdbc.*;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author Sap
 */
public class MySqlConnection {

    private Connection connection;
    private Statement statement;
    private static final String driver = "org.gjt.mm.mysql.Driver";
    private static String dbURL = "jdbc:mysql://127.0.0.1:3306/ecads";
    private static final String login = "root";
    private static final String password = "";

    /*************************************************************************************/
    //CONSTRUCTOR
    public MySqlConnection(String loginString, String passwordString) {
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(dbURL, loginString, passwordString);
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        } catch (ClassNotFoundException e) {
            System.err.println("Oracle Connection Bean: driver unavailable");
            connection = null;
            
        } catch (SQLException e) {
            System.err.println("Oracle Connection Bean: sql exception..." + e.getMessage());
            e.printStackTrace();
            connection = null;
        }

    }// constructor
    /*************************************************************************************/
    //CONSTRUCTOR
    public MySqlConnection() {
        this(login, password);
    }// constructor
    /*************************************************************************************/
    public Connection getConnection() {
        return connection;
    }
    
    public void clear() {
        try {
            connection.clearWarnings();
            statement.clearBatch();
            statement.clearBatch();
        } catch (SQLException ex) {
            
        }
    }

    /*************************************************************************************/
    public void commit() throws SQLException {
        connection.commit();
    }

    /*************************************************************************************/
    public void rollback() throws SQLException {
        connection.rollback();
    }

    /*************************************************************************************/
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        connection.setAutoCommit(autoCommit);
    }

    /*************************************************************************************/
    public ResultSet executeQuery(String sql) throws SQLException {
        return statement.executeQuery(sql);
    }

    /*************************************************************************************/
    public int executeInsert(String sql) throws SQLException {
        int id =0;
            statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
        ResultSet rs = statement.getGeneratedKeys();
        while(rs.next()){
            id= rs.getInt(1);
        }
        return id;
    }

    /*************************************************************************************/
    //MEthod to execute DDL sql query - insert, update and delete
    public int executeUpdate(String sql) throws SQLException {
        return statement.executeUpdate(sql);
    }

    /*************************************************************************************/
    /*
    public void close() throws SQLException {
    System.out.println("in close method of OracleConnection.java");
    if (statement !=null) {
    statement.close();
    statement = null;
    }
    if(connection!=null && connection.isClosed() == true) {
    connection = null;
    }
    else if (connection!=null) {
    connection.close();
    connection = null;
    }
    }
     */
    /*************************************************************************************/
    public void close() throws SQLException {
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }

    /*************************************************************************************/
    public void safeClose() {
        try {
            if (statement != null) {
                statement.close();
            }

            if (connection != null && connection.isClosed() == false) {
                connection.close();
            }
        } catch (SQLException sqe) {
            System.out.println("SQLException occured during safeClose Method");
            sqe.printStackTrace();
        } finally {
            if ((connection != null)) {
                try {
                    if (statement != null) {
                        statement.close();
                    }
                    connection.close();

                } catch (SQLException e) {
                    System.out.println("Error in finally safe close: " + e.toString());
                }
            }
        }
    }

    /*************************************************************************************/
    public boolean isClosed() throws SQLException {
        if (connection != null) {
            return connection.isClosed();
        } else {
            return false;
        }
    }

    public static Vector resultRowsToVector(ResultSet R, StringBuffer errMsg) {
        try {
            Vector V = new Vector();
            V.addElement(resultSetColumnName(R, errMsg));
            while (R.next()) {
                V.addElement(resultRowValues(R, errMsg));
            }
            return V;
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("resultRowsToVecter" + e);
            errMsg.append(e.toString());
            return null;
        }
    }

    public static String[] resultRowValues(ResultSet R, StringBuffer errMsg) throws SQLException {
        ResultSetMetaData rsmd = R.getMetaData();
        String S[] = new String[rsmd.getColumnCount()];
        for (int i = 0; i < S.length; i++) {
            S[i] = R.getString(i + 1);
        }
        return S;
    }

    public static String[] resultSetColumnName(ResultSet R, StringBuffer errMsg) throws SQLException {
        ResultSetMetaData rsmd = R.getMetaData();
        String S[] = new String[rsmd.getColumnCount()];
        for (int i = 0; i < S.length; i++) {
            S[i] = rsmd.getColumnName(i + 1);
        }
        return S;
    }

    public static void main(String[] args) {
        try {
            MySqlConnection aoracon = new MySqlConnection();
            System.out.println("OracleConnection is called ");
//        String sql = "select code, description from lacadDept order by code";
//        String sql = "select code, description from LACADDEPT order by code";
            String sql = "select * from adstbl";

            System.out.println(sql);
            ResultSet rs = aoracon.executeQuery(sql);
            StringBuffer errMsg = new StringBuffer();
            Vector v = resultRowsToVector(rs, errMsg);

            System.out.println(errMsg);
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}



